﻿using Microsoft.Practices.EnterpriseLibrary.Data;
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace DevTeam
{
    public enum ExecuteTypes
    {
        DataTable,
        DataSet,
        NonQuery,
        DataTableByQuery
    }

    public class DataManager
    {
        public Database Database = null;
        public string ConnectionString;
        public DataManager(string cs)
        {
            ConnectionString = cs;
            Database = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(cs);
        }

        private void InitCommand(DbConnection sqlConnection, DbCommand sqlCommand)
        {
            foreach (SqlParameter parameter in sqlCommand.Parameters)
            {
                if (parameter.SqlDbType != SqlDbType.Structured)
                    continue;

                string name = parameter.TypeName;
                int index = name.IndexOf(".");
                if (index == -1)
                    continue;

                name = name.Substring(index + 1);
                if (name.Contains("."))
                    parameter.TypeName = name;
            }

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandTimeout = 90;
            sqlCommand.Connection = sqlConnection;
        }

        public class KarbelXmlWriter : System.Xml.XmlTextWriter
        {
            public KarbelXmlWriter(System.IO.TextWriter w)
                : base(w)
            {
                //Formatting = System.Xml.Formatting.Indented;
            }

            //public override void WriteStartDocument()
            //{
            //    //to suppress
            //}
        }

        public class KarbelXmlReader : System.Xml.XmlTextReader
        {
            public KarbelXmlReader(System.IO.TextReader r)
                : base(r)
            {
                //Formatting = System.Xml.Formatting.Indented;
            }

            //public override void WriteStartDocument()
            //{
            //    //to suppress
            //}
        }

        public DataSet Fill(DataSet sourceDataSet, string SemiColonSeperatedTableNames, string SPName, params object[] parameters)
        {
            using (DbConnection sqlConnection = Database.CreateConnection())
            {
                using (DbCommand sqlCommand = Database.GetStoredProcCommand(SPName, parameters))
                {
                    InitCommand(sqlConnection, sqlCommand);
                    //sqlCommand.CommandTimeout = int.MaxValue;

                    try
                    {
                        DbDataAdapter sqlda = Database.GetDataAdapter();
                        sqlda.SelectCommand = sqlCommand;

                        sourceDataSet.EnforceConstraints = false;

                        sqlda.TableMappings.Clear();
                        string[] Tables = SemiColonSeperatedTableNames.Split(';');

                        for (int i = 0; i < Tables.Length; i++)
                            sqlda.TableMappings.Add("Table" + (i > 0 ? i.ToString() : ""), Tables[i]);

                        sqlda.Fill(sourceDataSet);
                        //ds.AcceptChanges();  //yan etkisi olur mu, düşün!
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }
                }
            }
            return sourceDataSet;
        }

        public DataSet ExecuteDataSet(string SPName, params object[] parameters)
        {
            using (DbConnection sqlConnection = Database.CreateConnection())
            {
                using (DbCommand sqlCommand = Database.GetStoredProcCommand(SPName, parameters))
                {
                    InitCommand(sqlConnection, sqlCommand);

                    try
                    {
                        //(sqlConnection as SqlConnection).StatisticsEnabled = true;              

                        DataSet dataset = new DataSet();
                        dataset.EnforceConstraints = false;
                        DbDataAdapter sqlda = Database.GetDataAdapter();
                        sqlda.SelectCommand = sqlCommand;
                        sqlda.Fill(dataset);

                        //System.Collections.IDictionary statistics = (sqlConnection as SqlConnection).RetrieveStatistics();
                        //AppSession.MainForm.NotifyMain(string.Format("BytesReceived: {0:###,#}", statistics["BytesReceived"]));
                        return dataset;
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }

                }
            }
        }

        public DataTable ExecuteDataTable(string SPName, params object[] parameters)
        {
            using (DbConnection sqlConnection = Database.CreateConnection())
            {
                using (DbCommand sqlCommand = Database.GetStoredProcCommand(SPName, parameters))
                {
                    InitCommand(sqlConnection, sqlCommand);

                    try
                    {
                        DataTable dt = new DataTable("Table");
                        DbDataAdapter sqlda = Database.GetDataAdapter();
                        sqlda.SelectCommand = sqlCommand;
                        dt.BeginLoadData();
                        sqlda.Fill(dt);
                        dt.EndLoadData();
                        return dt;
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }
                }
            }
        }

        public DataTable ExecuteDataTableByQuery(string query)
        {
            using (DbConnection sqlConnection = Database.CreateConnection())
            {
                using (DbCommand sqlCommand = Database.GetSqlStringCommand(query))
                {
                    InitCommand(sqlConnection, sqlCommand);
                    sqlCommand.CommandType = CommandType.Text;

                    try
                    {
                        DataTable dt = new DataTable("Table");
                        DbDataAdapter sqlda = Database.GetDataAdapter();
                        sqlda.SelectCommand = sqlCommand;
                        dt.BeginLoadData();
                        sqlda.Fill(dt);
                        dt.EndLoadData();
                        return dt;
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }
                }
            }
        }

        public object ExecuteScalar(string SPName, params object[] parameters)
        {
            using (DbConnection sqlConnection = Database.CreateConnection())
            {
                //Database.ClearParameterCache();

                using (DbCommand sqlCommand = Database.GetStoredProcCommand(SPName, parameters))
                {
                    try
                    {
                        sqlConnection.Open();
                        InitCommand(sqlConnection, sqlCommand);

                        return sqlCommand.ExecuteScalar();
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }
                }
            }
        }

        public int ExecuteNonQuery(string SPName, params object[] parameters)
        {
            using (DbConnection sqlConnection = Database.CreateConnection())
            {
                using (DbCommand sqlCommand = Database.GetStoredProcCommand(SPName, parameters))
                {
                    try
                    {
                        sqlConnection.Open();
                        InitCommand(sqlConnection, sqlCommand);

                        return sqlCommand.ExecuteNonQuery();
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }
                }
            }
        }

        public DataTable ExecuteReader(string SPName, params object[] parameters)
        {
            using (DbConnection sqlConnection = Database.CreateConnection())
            {
                using (DbCommand sqlCommand = Database.GetStoredProcCommand(SPName, parameters))
                {
                    try
                    {
                        sqlConnection.Open();
                        InitCommand(sqlConnection, sqlCommand);

                        DbDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.SingleResult);

                        DataTable dt = new DataTable();
                        dt.Load(reader);

                        return dt;
                    }
                    catch (Exception Ex)
                    {
                        throw (Ex);
                    }
                }
            }
        }

        public SP RunSP(ExecuteTypes executeType, Action<SP> CompletedMethod1, Action<SP> CompletedMethod2, string nameOrQuery, params object[] parameters)
        {
            SP sp = new SP(nameOrQuery, parameters);
            sp.Run(executeType, CompletedMethod1, CompletedMethod2, sp_DoWork);
            return sp;
        }

        void sp_DoWork(DataManager.SP sp)
        {
            if (sp.ExecuteType == ExecuteTypes.DataTable)
                sp.DataTable = ExecuteDataTable(sp.Name, sp.Params);
            else if (sp.ExecuteType == ExecuteTypes.DataSet)
                sp.DataSet = ExecuteDataSet(sp.Name, sp.Params);
            else if (sp.ExecuteType == ExecuteTypes.NonQuery)
                ExecuteNonQuery(sp.Name, sp.Params);
            else if (sp.ExecuteType == ExecuteTypes.DataTableByQuery)
                sp.DataTable = ExecuteDataTableByQuery(sp.Name);
        }

        public class SP
        {
            //public delegate void CompletedHandler(SP sp);
            //public event CompletedHandler Completed;
            //public event CompletedHandler DoWork;

            public SP(string nameOrQuery, params object[] parameters)
            {
                this.Name = nameOrQuery;
                this.Params = parameters;
            }

            public string Name;
            public object[] Params;
            public DataTable DataTable { get; set; }
            public DataSet DataSet { get; set; }

            public ExecuteTypes ExecuteType;


            public SP Run(ExecuteTypes executeType, Action<SP> completedMethod1, Action<SP> completedMethod2, Action<SP> doWorkMethod)
            {
                this.CompletedMethod1 = completedMethod1;
                this.CompletedMethod2 = completedMethod2;
                this.DoWorkMethod = doWorkMethod;
                this.ExecuteType = executeType;

                System.ComponentModel.BackgroundWorker bwAsyncExecute;
                bwAsyncExecute = new System.ComponentModel.BackgroundWorker();

                bwAsyncExecute.DoWork += bwAsyncExecute_DoWork;
                bwAsyncExecute.RunWorkerCompleted += bwAsyncExecute_RunWorkerCompleted;
                bwAsyncExecute.RunWorkerAsync(this);

                return this;
            }

            void bwAsyncExecute_RunWorkerCompleted(object sender, System.ComponentModel.RunWorkerCompletedEventArgs e)
            {
                CompletedMethod1(this);
                CompletedMethod2(this);

                //if (Completed != null)
                //    Completed(this);
            }


            void bwAsyncExecute_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
            {
                DoWorkMethod(this);
            }

            public Action<SP> CompletedMethod1 { get; set; }
            public Action<SP> CompletedMethod2 { get; set; }
            public Action<SP> DoWorkMethod { get; set; }
        }

    }
}
